package com.qdlc.p2p.dal.dao.impl;

import java.util.List;

import javax.persistence.Query;

import org.springframework.stereotype.Repository;

import com.qdlc.p2p.common.constant.Global;
import com.qdlc.p2p.common.constant.UpRateCollectionStatus;
import com.qdlc.p2p.common.util.NumberUtil;
import com.qdlc.p2p.dal.common.dao.jpa.BaseDaoImpl;
import com.qdlc.p2p.dal.dao.UpRateCollectionDao;
import com.qdlc.p2p.dal.dto.UpRateCollection;
import com.qdlc.p2p.dal.dto.User;

/**
 * 加息券产生待收差额DAO接口
 *
 * @author wj
 * @version 2.0
 * @since 2015-04-22
 */
@Repository("upRateCollectionDao")
public class UpRateCollectionDaoImpl extends BaseDaoImpl<UpRateCollection> implements UpRateCollectionDao {

    @SuppressWarnings("unchecked")
    @Override
    public UpRateCollection getUpRateCollectionById(long id) {
        String jpql = "from UpRateCollection where id = ?1";
        Query query = em.createQuery(jpql);
        query.setParameter(1, id);
        List<UpRateCollection> list = query.getResultList();
        if (list != null && list.size() >= 0) {
            return list.get(0);
        } else {
            return null;
        }
    }

    @Override
    public List<UpRateCollection> getUpRateCollection(long borrowId, int period) {
        String jpql = "from UpRateCollection where borrowId = ?1 and period =?2 and status = 0";
        Query query = em.createQuery(jpql);
        query.setParameter(1, borrowId);
        query.setParameter(2, period);
        List<UpRateCollection> list = query.getResultList();
        return list;
    }

    @Override
    public double upRateCollectionSum(long borrowId, int period) {
        StringBuffer sql = new StringBuffer("SELECT IFNULL(SUM(difference_interest),0)");
        sql.append(" from ");
        sql.append(Global.DB_PREFIX).append("additional_up_rate_collection");
        sql.append(" where borrow_id = :borrow_id");
        sql.append("   and period = :period");
        sql.append("   and status = " + UpRateCollectionStatus.NO_REPAYMENT);
        Query query = em.createNativeQuery(sql.toString());
        query.setParameter("borrow_id", borrowId);
        query.setParameter("period", period);
        Object obj = query.getSingleResult();
        return Double.parseDouble(obj.toString());
    }

    @Override
    public UpRateCollection getUpRateCollectionByCId(long collectionId) {
        String jpql = "from UpRateCollection where collectionId = ?1";
        Query query = em.createQuery(jpql);
        query.setParameter(1, collectionId);
        @SuppressWarnings("unchecked")
        List<UpRateCollection> list = query.getResultList();
        if (null != list && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

    @Override
    public double getUpRateCollection(String year, String month, int status) {
        String sql = "SELECT SUM(difference_interest) FROM " + Global.DB_PREFIX + "additional_up_rate_collection WHERE DATE_FORMAT(repayment_yes_time,'%Y') = '" + year + "' AND DATE_FORMAT(repayment_yes_time,'%m') = '" + month + "' AND status = " + status;
        Query query = em.createNativeQuery(sql);
        Object obj = query.getSingleResult();
        if (obj != null) {
            return Double.parseDouble(obj.toString());
        }
        return 0;
    }

    @Override
    public double getUpRateInterestByCollectionId(long collectionId) {
        String jql = "select differenceInterest from UpRateCollection where collectionId = ?1";
        Query query = em.createQuery(jql).setParameter(1, collectionId);
        Object obj = query.getSingleResult();
        return obj != null ? NumberUtil.getDouble(obj.toString()) : 0;
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Object[]> getRateCollectionInterest(User user, String date) {
        String sql = "select DATE_FORMAT(repayment_time,'%m-%d'),sum(difference_interest) from " + Global.DB_PREFIX + "additional_up_rate_collection where status = 0  and DATE_FORMAT(repayment_time,'%Y-%m') = :timeDate and user_id = :userId group by repayment_time ";
        Query q = em.createNativeQuery(sql).setParameter("timeDate", date).setParameter("userId", user.getUserId());
        return q.getResultList();
    }

}
